/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package TableModel;

import Connection.Koneksi;
import Entities.Sewa;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Adrian
 */
public class Persewaan_List {

    private static Connection connection;
    private static Persewaan_List kendaraanList;

    public Persewaan_List(Connection connection) {
        //inisialisasi conn
        this.connection = connection;
    }

    public static Persewaan_List getKendaraanList() {
        //untuk mengambil status koneksi dari kelas Koneksi
        if (kendaraanList == null) {
            kendaraanList = new Persewaan_List(Koneksi.getConnection());
        }
        return kendaraanList;
    }

    public List<Sewa> getAllPersewaan() throws SQLException {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        connection.setAutoCommit(false);
        String sql = "select no_faktur,nama_penyewa,alamat_penyewa,no_telp_penyewa,tanggal_sewa,uang_sewa,tanggal_kembali,denda,no_polisi,round(sysdate - tanggal_kembali)-1 from sewa where denda='"+-1+"'";

        preparedStatement = connection.prepareStatement(sql);
        resultSet = preparedStatement.executeQuery();
        List<Sewa> sewas = new ArrayList<Sewa>();

        while (resultSet.next()) {
            try {
                Sewa sewa = new Sewa();
                SimpleDateFormat simpleDateFormat = new SimpleDateFormat("dd-MMM-yy");
                sewa.setNo_faktur(resultSet.getString(1));
                sewa.setNama_penyewa(resultSet.getString(2));
                sewa.setAlamat_penyewa(resultSet.getString(3));
                sewa.setNo_telp_penyewa(resultSet.getString(4));
                sewa.setTanggal_sewa(simpleDateFormat.format(resultSet.getDate(5)));
                sewa.setUang_sewa(Long.parseLong(resultSet.getString(6)));
                sewa.setTanggal_kembali(simpleDateFormat.format(resultSet.getDate(7)));
                sewa.setDenda(Long.parseLong(resultSet.getString(8)));
                sewa.setNo_Polisi(resultSet.getString(9));
                sewa.setvKeterlambatan(resultSet.getString(10));
                sewas.add(sewa);
            } catch (Exception ex) {
                Logger.getLogger(Persewaan_List.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        connection.commit();
        return sewas;
    }

    public List<Sewa> getCariPersewaan(String noSewa) throws SQLException {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        connection.setAutoCommit(false);
        String sql = "select no_faktur,nama_penyewa,alamat_penyewa,no_telp_penyewa,tanggal_sewa,uang_sewa,tanggal_kembali,denda,no_polisi,round(sysdate - tanggal_kembali)-1 "
                + "from sewa where no_faktur like '%" + noSewa + "' or no_faktur like '%" + noSewa + "%' or no_faktur like '" + noSewa + "%'";

        preparedStatement = connection.prepareStatement(sql);
        resultSet = preparedStatement.executeQuery();
        List<Sewa> sewas = new ArrayList<Sewa>();

        while (resultSet.next()) {
            try {
                Sewa sewa = new Sewa();
                SimpleDateFormat simpleDateFormat = new SimpleDateFormat("dd-MMM-yy");
                sewa.setNo_faktur(resultSet.getString(1));
                sewa.setNama_penyewa(resultSet.getString(2));
                sewa.setAlamat_penyewa(resultSet.getString(3));
                sewa.setNo_telp_penyewa(resultSet.getString(4));
                sewa.setTanggal_sewa(simpleDateFormat.format(resultSet.getDate(5)));
                sewa.setUang_sewa(Long.parseLong(resultSet.getString(6)));
                sewa.setTanggal_kembali(simpleDateFormat.format(resultSet.getDate(7)));
                if (resultSet.getString(8) == null) {
                    sewa.setDenda(0);
                } else {
                    sewa.setDenda(Long.parseLong(resultSet.getString(8)));
                }
                sewa.setNo_Polisi(resultSet.getString(9));
                sewa.setvKeterlambatan(resultSet.getString(10));
                sewas.add(sewa);
            } catch (Exception ex) {
                Logger.getLogger(Persewaan_List.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        connection.commit();
        return sewas;
    }

    public List<Sewa> getPersewaanRekap(String tanggal) throws SQLException {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        connection.setAutoCommit(false);
        String sql = "select * from sewa where tanggal_sewa like '%" + tanggal + "'";
        preparedStatement = connection.prepareStatement(sql);
        resultSet = preparedStatement.executeQuery();
        List<Sewa> sewas = new ArrayList<Sewa>();

        while (resultSet.next()) {
            try {
                Sewa sewa = new Sewa();
                SimpleDateFormat simpleDateFormat = new SimpleDateFormat("dd-MM-yyyy");
                sewa.setNo_faktur(resultSet.getString(1));
                sewa.setNama_penyewa(resultSet.getString(2));
                sewa.setAlamat_penyewa(resultSet.getString(3));
                sewa.setNo_telp_penyewa(resultSet.getString(4));
                sewa.setTanggal_sewa(simpleDateFormat.format(resultSet.getDate(5)));
                sewa.setUang_sewa(Long.parseLong(resultSet.getString(6)));
                sewa.setTanggal_kembali(simpleDateFormat.format(resultSet.getDate(7)));
                if (resultSet.getString(8) == null) {
                    sewa.setDenda(0);
                } else {
                    sewa.setDenda(Long.parseLong(resultSet.getString(8)));
                }
                sewa.setNo_Polisi(resultSet.getString(9));
                sewa.setvKeterlambatan(String.valueOf(resultSet.getInt(10)));
                sewas.add(sewa);
            } catch (Exception ex) {
                Logger.getLogger(Persewaan_List.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        connection.commit();
        return sewas;
    }
}
